package com.szholly.pro.watergas_server.ps.schedule;




import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.szholly.data.general.RowBase;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.RedisHelper;
import org.apache.log4j.Logger;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import lrq.common.MyLog;
/**
 *  定时任务，每天定时生成 周、月、季、年的数据（数据来源：天数据）
 * @author 张文成
 *
 */
public class CreateStatisticalData  implements Job {
	static Logger log;
	
	@Override
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		
		if(log == null){
			log = MyLog.getLogger("CreateStatisticalData");
		}
		 
		try {
		   Calendar cal=Calendar.getInstance();
		   Calendar realCal = Calendar.getInstance();
		   
		   //更新日数据(更新天数据，更新OVER_COUNT,FAULT_COUNT)
		   realCal.setTime(cal.getTime());  
		   UpdateDayData(realCal);
		   //生成周数据
		   realCal.setTime(cal.getTime());  
	       CreateWeekData(realCal);
	       //生成月数据
	       realCal.setTime(cal.getTime());  
	       CreateMonthData(realCal);  
	       //生成季数据
	       realCal.setTime(cal.getTime());  
	       CreateQuarterData(realCal);
	       //生成年数据
	       realCal.setTime(cal.getTime());  
	       CreateYearData(realCal);
		   
	       //生成去年一年的数据
//		   Calendar calCompare=Calendar.getInstance();
//		   cal.add(Calendar.DAY_OF_YEAR, -120);
//		   while(!cal.after(calCompare))
//		   { 
//			   cal.add(Calendar.DAY_OF_YEAR, 1);
//		   	   realCal.setTime(cal.getTime());  
//		   	   UpdateDayData(realCal);
//			   realCal.setTime(cal.getTime());  
//		       CreateWeekData(realCal);
//		       realCal.setTime(cal.getTime());  
//		       CreateMonthData(realCal);  
//		       realCal.setTime(cal.getTime());  
//		       CreateQuarterData(realCal);
//		       realCal.setTime(cal.getTime());  
//		       CreateYearData(realCal);
//		       log.info(cal.get(Calendar.DAY_OF_YEAR));
//		   }
		   
		} catch (Exception e) {
			log.error("生成报警信息出错",e);
		}
	}
	
	/**
	 * 更新天数据，更新OVER_COUNT,FAULT_COUNT
	 * 张文成
	 * 2016年12月30日
	 */
	private void UpdateDayData(Calendar cal){
		log.info("开始更新天数据");
		try{
//	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.DAY_OF_MONTH, -1);
			Date startTime = cal.getTime();	       
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String strStartTime = sdf.format(startTime);
			String strEndTime = sdf.format(endTime);

	        //循环站点    污水、地表水所有站点  
	        String sql = "select t1.id,t1.device_num from BC_COLLECTION_INFO t1 "
	        		+ " left join BC_SITE_INFO t2 on t1.SITE_ID=t2.exp_no"
	        		+ " where t2.STATION_CATEGORY in('4') order by t2.station_category";
			List<RowBase> rows = DataUtil.getSelectRows(sql, null);
			if(rows.size()>0){
				for(int i=0;i<rows.size();i++){
					RowBase row = rows.get(i);
					String strMN = row.getSafeString("device_num");
					sql = "update BD_MO_DAY t set OVER_COUNT = ";
					sql += "(select count(*) from (select count(*) from BD_MO_HOUR t where t.OVER_FLAG = 'O' and t.mn = '"+strMN+"'"
							+ " and t.datatime >= to_date('"+strStartTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
							+ " and t.datatime < to_date('"+strEndTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')"
							+ " group by t.qn))";
					sql += " ,FAULT_COUNT = ";
					sql += "(select count(*) from (select count(*) from BD_MO_HOUR t where t.OVER_FLAG = 'D' and t.mn = '"+strMN+"' "
							+ " and t.datatime >= to_date('"+strStartTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
				    		+ " and t.datatime < to_date('"+strEndTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')"
				    		+ " group by t.qn))";
					sql += " where 1=1 ";
				    sql += " and t.datatime >= to_date('"+strStartTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
				    		+ " and t.datatime < to_date('"+strEndTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ";
					DataUtil.ExecuteUpdateSql(sql, null);
				}
			}
		} catch(Exception e)
		{
			log.error("更新天数据出错",e);
		}
	}
	
	
	/**
	 * 生成周数据
	 * 张文成
	 * 2016年12月30日
	 */
	private void CreateWeekData(Calendar cal){
		log.info("开始生成周数据");
		try{
			//判断当前时间是否为周一
	        if(cal.get(Calendar.DAY_OF_WEEK) != Calendar.MONDAY)
	        {
	        	return;
	        }
	        
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.DAY_OF_MONTH, -7);
			Date startTime = cal.getTime();
	       
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String strStartTime = sdf.format(startTime);
			String strEndTime = sdf.format(endTime);
			//设置周一为一周的开头,week=1时，应设置为53周
			cal.setFirstDayOfWeek(Calendar.MONDAY);
			int week = cal.get(Calendar.WEEK_OF_YEAR);
			if(week == 1)
			{
				week = 53;
			}
			
			String strDATE_TEXT = cal.get(Calendar.YEAR)+"年"+week+"周";	
			CreateData(strStartTime,strEndTime,strDATE_TEXT,"BD_MO_RPT_WEEK");
		} catch(Exception e)
		{
			log.error("生成周数据出错",e);
		}
	}
	
	
	/**
	 * 生成月数据
	 * 张文成
	 * 2017年01月03日
	 */
	private void CreateMonthData(Calendar cal){
		log.info("开始生成月数据");
		try{
			//判断当前时间是否为1号
	        if(cal.get(Calendar.DAY_OF_MONTH) != 1)
	        {
	        	return;
	        }
	        
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.MONTH, -1);
			Date startTime = cal.getTime();
	       
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String strStartTime = sdf.format(startTime);
			String strEndTime = sdf.format(endTime);
		
			String strMonth = String.format("%0" + 2 + "d", cal.get(Calendar.MONTH)+1);
			String strDATE_TEXT = cal.get(Calendar.YEAR)+"年"+strMonth+"月";
			CreateData(strStartTime,strEndTime,strDATE_TEXT,"BD_MO_MONTH");
		} catch(Exception e)
		{
			log.error("生成月数据出错",e);
		}
	}
	
	/**
	 * 生成季数据
	 * 张文成
	 * 2017年01月03日
	 */
	private void CreateQuarterData(Calendar cal){
		log.info("开始生成季数据");
		try{

			//判断当前时间是否为1号,是否为季度的第一个月
	        if(cal.get(Calendar.DAY_OF_MONTH) != 1 || (cal.get(Calendar.MONTH)%3)!=0)
	        {
	        	return;
	        }
	             
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.MONTH, -3);
			Date startTime = cal.getTime();
	       
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String strStartTime = sdf.format(startTime);
			String strEndTime = sdf.format(endTime);
			String strDATE_TEXT = cal.get(Calendar.YEAR)+"年"+getQuarter(cal)+"季";
			CreateData(strStartTime,strEndTime,strDATE_TEXT,"BD_MO_QUARTER");	
		} catch(Exception e)
		{
			log.error("生成季数据出错",e);
		}
	}
	
	
	/**
	 * 生成年数据
	 * 张文成
	 * 2017年01月03日
	 */
	private void CreateYearData(Calendar cal){
		log.info("开始生成年数据");
		try{
			//判断当前时间当年第一天
	        if(cal.get(Calendar.DAY_OF_YEAR) != 1)
	        {
	        	return;
	        }
	           
	        //获取开始时间与结束时间
			Date endTime = cal.getTime();
			cal.add(Calendar.YEAR, -1);
			Date startTime = cal.getTime();
	       
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String strStartTime = sdf.format(startTime);
			String strEndTime = sdf.format(endTime);
			String strDATE_TEXT = cal.get(Calendar.YEAR)+"年";
			
			CreateData(strStartTime,strEndTime,strDATE_TEXT,"BD_MO_YEAR");	
		} catch(Exception e)
		{
			log.error("生成年数据出错",e);
		}
	}
	
	
	/**
	 * 生成数据
	 * 张文成
	 * 2017年01月03日
	 */
	private void CreateData(String strStartTime,String strEndTime,String strDATE_TEXT,String strTableName)throws Exception{
	        //循环站点    污水、地表水所有站点  
	        String sql = "select t1.id,t1.device_num from BC_COLLECTION_INFO t1 "
	        		+ " left join BC_SITE_INFO t2 on t1.SITE_ID=t2.exp_no"
	        		+ " where t2.STATION_CATEGORY in('4') order by t2.station_category";
			List<RowBase> rows = DataUtil.getSelectRows(sql, null);
			if(rows.size()>0){
				for(int i=0;i<rows.size();i++){
					RowBase row = rows.get(i);
					String strMN = row.getSafeString("device_num");
					//循环站点所有个参数
					sql = "select t.parmname from BD_MO_DAY t where t.mn = '"+strMN+"' group by t.parmname";
					List<RowBase> parmRows = DataUtil.getSelectRows(sql, null);
					if(parmRows.size()>0){
						for(int j=0;j<parmRows.size();j++){
							RowBase parmRow = parmRows.get(j);
							String strOver_Flag = "";
							String strFlag = "";
							String strParm = parmRow.getSafeString("parmname");
							Double abnormal_up = RedisHelper.getInstance().getPollAbnormalUp(strMN, strParm);
							Double abnormal_down = RedisHelper.getInstance().getPollAbnormalDown(strMN, strParm);
							Double alarm_up = RedisHelper.getInstance().getPollAlarmUp(strMN, strParm);
							Double alarm_down = RedisHelper.getInstance().getPollAlarmUp(strMN, strParm);
							
							sql = "select avg(t.avg) as valueD from BD_MO_DAY t where t.mn = '"+strMN+"' and t.parmname = '"+strParm+"'"
									+ " and t.datatime >= to_date('"+strStartTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
									+ " and t.datatime < to_date('"+strEndTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ";
							List<RowBase> avgRows = DataUtil.getSelectRows(sql, null);
							RowBase avgRow = avgRows.get(0);
							Double valueD = avgRow.getSafeDouble("valueD");
							//增加超标判断
							if(alarm_down != null && valueD < alarm_down)
							{
								//若超出报警下限，则设置OVER_FLAG为O
								strOver_Flag = "O";
							}
							else if(alarm_up != null && valueD > alarm_up)
							{
								//若超出报警上限，则设置OVER_FLAG为O
								strOver_Flag = "O";
							}	
							
							//异常判断
							if(abnormal_down != null && valueD < abnormal_down)
							{
								//若超出异常下限，则设置OVER_FLAG为D
								strOver_Flag = "D";
							}
							else if(abnormal_up != null && valueD > abnormal_up)
							{
								//若超出异常上限，则设置OVER_FLAG为D
								strOver_Flag = "D";
							}	
						
							if(valueD > 999999999999.0)
							{
								strOver_Flag = "D";
							}
							
							//不超标和异常，则设置Flag为N
							if("".equals(strOver_Flag))
							{
								strFlag = "N";
							}
							
							sql = "insert into "+strTableName+"(GUID,DATATIME,DATE_TEXT,MN,PARMNAME,COU,AVG,MAX,MIN,OVER_COUNT,FAULT_COUNT,FLAG,OVER_FLAG)";
						    sql += " select sys_guid(),to_date('"+strStartTime+"','yyyy-mm-dd') as datatime,'"+strDATE_TEXT+"' as DATE_TEXT,t.mn,t.parmname,"
						    		+ "round(case when sum(t.cou) < 999999999999 then sum(t.cou) else 999999999999 end,3),"
						    		+ "round(case when avg(t.avg) < 999999999999 then avg(t.avg) else 999999999999 end,3),"
						    		+ "round(case when max(t.avg) < 999999999999 then max(t.avg) else 999999999999 end,3),"
						    		+ "round(case when min(t.avg) < 999999999999 then min(t.avg) else 999999999999 end,3),";
						    sql += "(select count(*) from (select count(*) from BD_MO_DAY t where t.OVER_FLAG='O' AND t.mn = '"+strMN+"' and t.parmname = '"+strParm+"'"
						    		+ " and t.datatime >= to_date('"+strStartTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
						    		+ " and t.datatime < to_date('"+strEndTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')"
						    		+ " group by t.qn)),";
						    sql += "(select count(*) from (select count(*) from BD_MO_DAY t where t.OVER_FLAG='D' AND t.mn = '"+strMN+"' and t.parmname = '"+strParm+"'"
						    		+ " and t.datatime >= to_date('"+strStartTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
						    		+ " and t.datatime < to_date('"+strEndTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')"
						    		+ " group by t.qn)),'"+strFlag+"','"+strOver_Flag+"'";						    	
						    sql += " from BD_MO_DAY t ";
						    sql += " where t.mn = '"+strMN+"' and t.parmname = '"+strParm+"' ";
						    sql += " and t.datatime >= to_date('"+strStartTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') "
						    		+ " and t.datatime < to_date('"+strEndTime+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ";
						    sql += " group by t.mn,t.parmname";
						    DataUtil.ExecuteUpdateSql(sql, null);
						}
					}
				}
			}  	
	}
	
	
	
    /** 
     * 获得当前季度
     * 1 第一季度 2 第二季度 3 第三季度 4 第四季度 
     *  
     * @param date 
     * @return 
     */  
    public static int getQuarter(Calendar cal) {  
  
        int quarter = 0;  
  
        //Calendar cal = Calendar.getInstance();  
        int month = cal.get(Calendar.MONTH);  
        switch (month) {  
        case Calendar.JANUARY:  
        case Calendar.FEBRUARY:  
        case Calendar.MARCH:  
        	quarter = 1;  
            break;  
        case Calendar.APRIL:  
        case Calendar.MAY:  
        case Calendar.JUNE:  
        	quarter = 2;  
            break;  
        case Calendar.JULY:  
        case Calendar.AUGUST:  
        case Calendar.SEPTEMBER:  
        	quarter = 3;  
            break;  
        case Calendar.OCTOBER:  
        case Calendar.NOVEMBER:  
        case Calendar.DECEMBER:  
        	quarter = 4;  
            break;  
        default:  
            break;  
        }  
        return quarter;  
    }  
    
	 /**
     * 获取当年的第一天
     * @param year
     * @return
     */
    public static Calendar getCurrYearFirst(){
        Calendar currCal=Calendar.getInstance();  
        int currentYear = currCal.get(Calendar.YEAR);
        Calendar calendar = Calendar.getInstance();
        calendar.clear();
        calendar.set(Calendar.YEAR, currentYear);
        return calendar;
    }
    
}
